const { conStr } = require("./config");
const mssql = require('mssql');
const { Transaction } = require("./help/sqlhelp")
const cheerio = require("cheerio");
const moment = require("moment");

const ADODB = require('node-adodb');
const path = require("path");

function htmlToString(htmlstr) {
  if (htmlstr == null || typeof htmlstr == "undefined") return "";
  let temp = htmlstr.replace(/<\/?.+?>/g, "");
  let result = temp.replace(/ /g, "").replace(/\"/g, "").replace(/&#xA0;/g, " ");//result为得到后的内容
  return result;
}

async function runType() {

  const accessCon = ADODB.open(`Provider=Microsoft.Jet.OLEDB.4.0;Data Source=${path.join(__dirname, "./db/myDb.mdb")};`);
  const data = await accessCon.query('SELECT * FROM ap_info_type').catch(err => {
    console.log("错误", err);
  });
  const arr = data.map(item => {
    const obj = JSON.parse(item.remark)
    return {
      id: item.info_type_id,
      infoTypeName: item.info_type_name,
      displayName: item.info_type_name,
      icon: null,
      treeIds: obj.fid == 0 ? `[]` : `["${obj.fid.toString()}"]`,
      orderNum: obj.orderid,
      isEnable: obj.enable ? 1 : 0,
      fid: obj.fid,
      rank: obj.fid == 0 ? 1 : 2,
      type: 1,
      href: null,
      remark: null,
    }
  })

  const msSqlcon = await mssql.connect(conStr).catch(err => {
    console.log("错误", err);
  });
  const result = await Transaction([
    "DELETE FROM [dbo].[apInfoType]",
    ...arr.map(item => `INSERT INTO apInfoType VALUES
      (
        ${item.id}
        ,'${item.infoTypeName}'
        ,'${item.displayName}'
        ,null
        ,'${item.treeIds}'
        ,${item.orderNum}
        ,${item.isEnable}
        ,${item.fid}
        ,${item.rank}
        ,${item.type}
        ,null
        ,null

      )
    `)
  ])
  console.log(result);
  msSqlcon.close();
  // const msSqlcon = await mssql.connect(conStr).catch(err => {
  //   console.log("错误", err);
  // });
  // const sqlstr = `
  //   SELECT * FROM sysUser
  // `
  // const result = (await mssql.query(sqlstr)).recordset;
  // msSqlcon.close();
  // console.log(result);

}
async function runInfo() {

  const accessCon = ADODB.open(`Provider=Microsoft.Jet.OLEDB.4.0;Data Source=${path.join(__dirname, "./db/myDb.mdb")};`);
  const data = await accessCon.query('SELECT * FROM ap_info').catch(err => {
    console.log("错误", err);
  });
  const arr = data.map(item => {
    const $ = cheerio.load(item.content, { decodeEntities: false });

    let bodySub = "";
    if ($("p").length != 0) {
      bodySub = $("p").eq(0).html();
    }
    bodySub = htmlToString(bodySub).replace(/\s/g, "");
    if (bodySub.length > 100) {
      bodySub = bodySub.substr(0, 100)
    }

    let cover = "";
    if ($("img").length != 0) {
      cover = $("img").eq(0).attr("src");
    }
    return {
      id: item.info_id,
      infoTypeId: item.info_type_id,
      createBy: 1,
      title: item.title,
      titleSub: null,
      body: item.content.replace(/>(\S)*\s+(\S)*</g, function (word) {
        if (!word.includes("=")) {
          return word.replace(/\s/g, "&nbsp;")
        }
        else {
          return word;
        }
      }).replace(/\'/g, "\'\'"),
      bodySub,
      cover,
      orderNum: item.view_num,

      createTime: moment(item.update_time).format('YYYY-MM-DD HH:mm:ss'),
      updateTime: moment(item.create_time).format('YYYY-MM-DD HH:mm:ss'),
      isEnable: item.is_enable,
    }
  })

  const msSqlcon = await mssql.connect(conStr).catch(err => {
    console.log("错误", err);
  });
  const result = await Transaction([
    "DELETE FROM [dbo].[apInfo]",
    ...arr.map(item => `INSERT INTO apInfo VALUES
      (
        ${item.id}
        ,${item.infoTypeId}
        ,1 
        ,'${item.title}' 
        ,null
        ,'${item.body}' 
        ,'${item.bodySub}' 
        ,'${item.cover}' 
        ,0
        ,${item.orderNum} 
        ,'${item.createTime}'
        ,'${item.updateTime}'
        ,${item.isEnable}
        ,null

      )
    `)
  ])
  console.log(result);
  msSqlcon.close();

}
runType();
runInfo();
